Dynamic dax to showcase only filtered values

[kkrj.ankit]

Hi All,

I’m not able to crack this, can someone advise, how should I achieve the below condition.

If my user selects a value from the Dynamic x-axis and the same has been given as filter like partners in this case.

I want to have a dynamic dax query which would only display the “Direct” (as selected here) bar and others should not be displayed. Similarly, if I select “Nature of Business” under dynamic x-axis and “bus_type” under filters then the user should be able to see a single bar of that selection.

Please advise. I’m also attaching my pbix.

Data Analysis.pbix (1.0 MB)

[Harsh]

Hello @kkrj.ankit,

Thank You for posting your query onto the Forum.

Well, is this the type of result that you’re looking for? Below is the screenshot provided for the reference -

In the above post, as you’ve suggested -

“If I select “Nature of Business” under dynamic x-axis and “bus_type” under filters then the user should be able to see a single bar of that selection.” - So I’ve highlighted those selections and achieved the results.

Now, in order to achieve this, I’d to make just a small change in the formula and i.e. here’s the original formula that you’ve written for the “Total Leads#” as mentioned below -

Total Leads# = 
IF( ISBLANK( DISTINCTCOUNT ( Loan_Data[code] ) ) , 0 , DISTINCTCOUNT ( Loan_Data[code] ) )

Now, since it includes a “0” in your formula, it showed the chart which were equal to 0 as well. I’ve just replaced the “0” with the “BLANK()” and below is the formula provided for the reference -

Total Leads# = 
IF( ISBLANK( DISTINCTCOUNT ( Loan_Data[code] ) ) , BLANK() , DISTINCTCOUNT ( Loan_Data[code] ) )

Also you can write the formula as follows, if you still want to include 0 but want to show greater than 0 results. Below is the formula provided for the reference -

Total Leads# = 
VAR TotalLeads = 
IF( ISBLANK( DISTINCTCOUNT ( Loan_Data[code] ) ) , 
    0 , 
    DISTINCTCOUNT ( Loan_Data[code] ) )

RETURN
IF( TotalLeads <= 0 , 
    BLANK() , 
    TotalLeads )

I’m also attaching the working of the PBIX file for the reference.

Hoping you find this useful and meets your requirements that you’ve been looking for.

Thanks and Warm Regards,
Harsh

Data Analysis.pbix (1.0 MB)

[kkrj.ankit]

Hi @Harsh

Thanks for your time on this. Silly me I missed that completely and was lost in other directions.

I also found out another solution, using dim tables for filtering instead of fact tables. Do you agree on that part? That would be the right approach correct?

Thanks,
Ankit

[Harsh]

Hello @kkrj.ankit,

You’re Welcome.

I’m glad that I was able to help you.

Well yes, I agree with that approach because almost more often than not we’ll see that filters/slicers will be considered from the “Dimension Tables” itself and not from the “Fact Table” because that’s the one of the main reason as well why we de-normalize our “Fact Table” and convert them into the “Dimension Tables”.

Well, you took me to my very early days of my Power BI where at that time I used to feel that it absolutely doesn’t matter from where my filters/slicers are coming from. But then gradually I understood how it actually works.

Thanks and Warm Regards,
Harsh

[kkrj.ankit]

Thanks @Harsh

Point learnt, will keep this in mind.

Thanks
Ankit