Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

Can you loop with PowerBI?

Hello!

 

I have the following data:

 

 Yes/NoIDSYSTEM
ABCY123A
ABCN123A
ABCY123A
ABCY123B

 

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!

Need Help
  • v-gizhi-msft's avatar
    v-gizhi-msft
    6 years ago

    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

5 Replies

  • az38's avatar
    az38
    Icon for Community Champion rankCommunity Champion
    6 years ago

    Anonymous 

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

  • mahoneypat's avatar
    mahoneypat
    Icon for Microsoft Employee rankMicrosoft Employee
    6 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago

      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's avatar
        v-gizhi-msft
        Icon for Community Support rankCommunity Support
        6 years ago

        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