Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi,
In my matrix table, I have values in columns and rows but when I select some brands from the slicer the matrix table is disappearing where are no values. Now, I want to see N/A where there are no values. I have tried with -
IF(ISBLANK(revenue), "N/A", revenue) but not working according to the requirement. Please find the screenshot
Solved! Go to Solution.
Hi @Koundinya ,
I created the data:
Here are the steps you can follow:
1. Create a calculated table.
Table 2 = DISTINCT('Table'[Column])
Table 3 = SUMMARIZE('Table','Table'[Row])
2. The relationship between the table and the table
3. Create measure.
Flag = IF(MAX('Table'[Row]) in SELECTCOLUMNS('Table 3',"1",'Table 3'[Row]),IF(HASONEVALUE('Table'[Value]),SUM('Table'[Value]),"NA"),"NA")
4. Use Table3's [Row] as the slicer, open the matrix, place Table[Flag] to Values, Table[Row] to Row, and Table2[Column] to Columns
5. Result:
Select an option of the slicer, the correct value is displayed in the matrix, and the value that is not displayed is NA
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi @Koundinya ,
I created the data:
Here are the steps you can follow:
1. Create a calculated table.
Table 2 = DISTINCT('Table'[Column])
Table 3 = SUMMARIZE('Table','Table'[Row])
2. The relationship between the table and the table
3. Create measure.
Flag = IF(MAX('Table'[Row]) in SELECTCOLUMNS('Table 3',"1",'Table 3'[Row]),IF(HASONEVALUE('Table'[Value]),SUM('Table'[Value]),"NA"),"NA")
4. Use Table3's [Row] as the slicer, open the matrix, place Table[Flag] to Values, Table[Row] to Row, and Table2[Column] to Columns
5. Result:
Select an option of the slicer, the correct value is displayed in the matrix, and the value that is not displayed is NA
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Koundinya What is not working?
Please refer "before Filters" Image attached above we are seeing Corporate, Public, Small Business Before filters applied. But after selecting some Brands in Slicer Public and Small Business is disappearing from the table. Now the requirement is to sell Public and Small Business as we with N/A though they don't have values
@Koundinya Ah, now I see. Well, you are likely running into an issue related to DAX aggressively pre-filtering data as much as possible. But, incredibly difficult to tell. Would need the PBIX or sample data and the measure formula.
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
User | Count |
---|---|
107 | |
88 | |
81 | |
76 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |