Can you loop with PowerBI?

[UsePowerBI]

Hello!

I have the following data:

	Yes/No	ID	SYSTEM
ABC	Y	123	A
ABC	N	123	A
ABC	Y	123	A
ABC	Y	123	B

I want to loop through all the SYSTEM=A instances and compare the Yes/No indicator with the SYSTEM=B instance.

I then want to count the rows where the is a Yes/No mismatch between SYSTEM A and B.

How can I do that?

Can PowerBI do looping?

Thanks!

[mahoneypat]

You can iterate through virtual tables with DAX, so what you are asking can be done.  Another approach would be to create two virtual tables for all A and B subtables and compare then with EXCEPT, INTERSECT, etc.  For example -

Mismatches =
VAR Atable =
FILTER (
SUMMARIZE ( Table, Table[System], Table[ID], Table[Yes/No] ),
Table[System] = "A"
)
VAR Btable =
FILTER (
SUMMARIZE ( Table, Table[System], Table[ID], Table[Yes/No] ),
Table[System] = "B"
)
RETURN
COUNTROWS ( EXCEPT ( Atable, Btable ) )

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[UsePowerBI]

Thanks all.

The output should be:

Matches: 2

Mismatches: 1

The match/mismatch is the Y/N indicator between all the System A records comparing to the System B record.

[v-gizhi-msft · Accepted solution]

Hi,

Please take following steps:

1)Create a slicer table as below by Enter Data:

2)Try this measure:

Measure = 
VAR a =
    CALCULATE ( MAX ( 'Table'[Yes/No] ), FILTER ( 'Table', 'Table'[SYSTEM] = "B" ) )
VAR b =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[SYSTEM] IN FILTERS ( 'Table'[SYSTEM] )
                && 'Table'[Yes/No] = a
                && 'Table'[SYSTEM] = "A"
        )
    )
VAR c =
    CALCULATE (
        COUNTROWS ( 'Table' ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[SYSTEM] IN FILTERS ( 'Table'[SYSTEM] )
                && 'Table'[Yes/No] <> a
                && 'Table'[SYSTEM] = "A"
        )
    )
RETURN
    SWITCH ( SELECTEDVALUE ( 'Table 2'[Slicer] ), "Matches", b, "Dismatches", c )

3)The result shows:

See my attached pbix file.

Best Regards,

Giotto

[amitchandak]

@UsePowerBI , earlier can help you in that. But I do not see any uniqueness to compare

https://www.red-gate.com/simple-talk/sql/bi/cracking-dax-the-earlier-and-rankx-functions/

[az38]

@UsePowerBI 

it's unclear, what is your desired result based on your dummy data?