Dynamic Row Count based on Dynamic Measure

[BK_PowerBI]

Dear All,

System 1 and System 2 are two different tables which gets distinct System value the main table 'Table1'

Created 2 tables with the below DAX

System 1 = SUMMARIZE('Table1', 'Table1'[System])

System 2 = SUMMARIZE('Table1', 'Table1'[System])

I have created these measures under each of these tables

Selected System 1 value = SELECTEDVALUE('System 1'[System])

Selected System 2 value = SELECTEDVALUE('System 2'[System])

I have 2 slicers based on the above 2 tables and based on the selections in each slicer - Value 1, Value 2 & Deviation measures are calculated.

Value 1 = CALCULATE(SUM('Table1'[Value]), FILTER('Table1', 'Table1'[System] = [Selected System 1 value]))

Value 2 = CALCULATE(SUM('Table1'[Value]), ALL('System 1'), USERELATIONSHIP('Table1'[System], 'System 2'[System]))

Deviation = DIVIDE(([Value 1] - [Value 2]),[Value 1],0)

I am highlighting the rows based on the below measure.

Color Highlight = IF(ABS([Deviation]) > 0.005, "#FFFF99", "FFFFFFFF")

    

Now the help needed is,

I need to calculate the count of highlighted rows dynamically based on all the Slicer selections (Date, System(s), KPI). If the count is zero, I have to display "All clear", otherwise I have to display the number of deviations showing (dynamically in the grid).

It would be very much appreciated if anyone could help me out please. 

Please let me know if my request is not clear or need more clarification.

Thank you in advance

[BK_PowerBI]

I think I forgot to mention about 1 more thing. I need the count, outside of the table, not to be part of the existing table. As you see from the screenshot in my previous post, I have brought in the count columns in the respective columns. I need to get the count of both the tables outside to be shown separately in another table visual as mentioned in the screenshot.

Sorry for missing the important requirement.

[Anonymous]

Hi @BK_PowerBI ,

According to your description, here are my steps you can follow as a solution.

(1) My test data is the same as yours.

(2) We can create a measure. 

Measure = 
 var _table= SUMMARIZE(ALLSELECTED('Table1'),[Date],[kpi],[System],"dev",ABS([Deviation]))
 var _rows=COUNTROWS(FILTER(_table,[dev] > 0.005))
 RETURN IF(_rows=0,"All clear",[Deviation])

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[BK_PowerBI]

Thank you for the suggestion.

I don't know how the temp table variable works in PowerBI.

I am attaching the pbix file (oops... where is the attach option. I am able to see only Image attach or Link. Do I need to upload somewhere and share the link ?)

Edit: Sharing the link now (hope links from jumpshare.com is allowed)

https://jmp.sh/SMW7dYGO 

Please guide me where I am going wrong.

Sanity Check = 
 VAR _TempTable = SUMMARIZE(ALLSELECTED('Reconciliation'), Reconciliation[Date],Reconciliation[KPI], "Cnt", [Sanity Count])
 var _RowCount = COUNTROWS(FILTER(_TempTable,[Cnt] = 1))
 --var _RowCount = COUNTROWS(_TempTable)
 RETURN
 _RowCount
-- IF(_RowCount=0,"All OK",_RowCount & " Warnings")

Trend Analysis = 
VAR _TempTable = SUMMARIZE(ALLSELECTED('Reconciliation'), Reconciliation[Date],Reconciliation[KPI], "Cnt", [Analysis Count])
 var _RowCount = COUNTROWS(FILTER(_TempTable,[Cnt] = 1))
 --var _RowCount = COUNTROWS(_TempTable)
 RETURN
 _RowCount
-- IF(_RowCount=0,"All OK",_RowCount & " Warnings")

Thank you once again.

[Anonymous · Accepted solution]

Hi @BK_PowerBI ,

Try this.

Sanity Check = 
 VAR _TempTable =ADDCOLUMNS(SUMMARIZE(ALLSELECTED(Reconciliation),[Date],[KPI]),"Cnt",[Sanity Count])
 var _RowCount = COUNTROWS(FILTER(_TempTable,[Cnt] = 1))
 --var _RowCount = COUNTROWS(_TempTable)
 RETURN
 _RowCount
-- IF(_RowCount=0,"All OK",_RowCount & " Warnings")

Trend Analysis = 
VAR _TempTable = ADDCOLUMNS(SUMMARIZE(ALLSELECTED('Reconciliation'), Reconciliation[Date],Reconciliation[KPI]), "Cnt", [Analysis Count])
 var _RowCount = COUNTROWS(FILTER(_TempTable,[Cnt] = 1))
 --var _RowCount = COUNTROWS(_TempTable)
 RETURN
 _RowCount
-- IF(_RowCount=0,"All OK",_RowCount & " Warnings")

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

[BK_PowerBI]

This "ADDCOLUMNS" did the trick.. I was breaking my head and you saved my day...
Thanks a million.