Forum Discussion

chetan8080's avatar
chetan8080
Helper II
1 year ago
Solved

Create a measure Based on Visual values

I need to create a measure where it gives me 1 or 0 based on values in a matrix visual, In the above picture the first two yellow highlighted values are unique in the matrix should have 1 as r...
Need Help
Tips and Tricks
  • v-sgandrathi's avatar
    v-sgandrathi
    1 year ago

    Hi chetan8080,


    I have used sample data and implemented the solution in Power BI Desktop. Please have a look."

     

    Try this DAX Measure:

    Unique In Visual =
    VAR CurrentValue = SELECTEDVALUE(Sheet1[Value])
    VAR VisibleRows =
        SUMMARIZE(
            ALLSELECTED(Sheet1),
            Sheet1[ID],
            Sheet1[Value]
        )
    VAR CountVisible =
        COUNTROWS(
            FILTER(
                VisibleRows,
                [Value] = CurrentValue
            )
        )
    RETURN IF(CountVisible = 1, 1, 0)
     
    I have included the PBIX file that I created using the provided sample data. Kindly review it and confirm whether it aligns with your expectations.

     

    I hope my suggestions provided valuable insights. If you have any further questions, don’t hesitate to ask in a follow-up message.
    If this post helped, please mark it as "Accept as Solution" so others can benefit as well.

     

    Best regards,
    Sahasra
    Communtiy Support Team.

chetan8080's avatar
chetan8080
Helper II
1 year ago

Hello SolomonovAnton , thank you for replying,
See below pictures where the ID is repeating but still DAX Returns me 1 instead of 0

The dax should  give 1 only when the ID is unique in the visual, if it gets repeated it should give 0.


Thanks,
Chetan J

 

  • v-sgandrathi's avatar
    v-sgandrathi
    Community Support
    1 year ago

    Hi chetan8080,


    I have used sample data and implemented the solution in Power BI Desktop. Please have a look."

     

    Try this DAX Measure:

    Unique In Visual =
    VAR CurrentValue = SELECTEDVALUE(Sheet1[Value])
    VAR VisibleRows =
        SUMMARIZE(
            ALLSELECTED(Sheet1),
            Sheet1[ID],
            Sheet1[Value]
        )
    VAR CountVisible =
        COUNTROWS(
            FILTER(
                VisibleRows,
                [Value] = CurrentValue
            )
        )
    RETURN IF(CountVisible = 1, 1, 0)
     
    I have included the PBIX file that I created using the provided sample data. Kindly review it and confirm whether it aligns with your expectations.

     

    I hope my suggestions provided valuable insights. If you have any further questions, don’t hesitate to ask in a follow-up message.
    If this post helped, please mark it as "Accept as Solution" so others can benefit as well.

     

    Best regards,
    Sahasra
    Communtiy Support Team.

    • chetan8080's avatar
      chetan8080
      Helper II
      1 year ago

      Hi v-sgandrathi ,
      thank you for your response.
      I tried your solution but it does not work for big data, please see attached image,

       

      First 4 rows are giving the correct results, the following rows even after having same ID and different PO gives false.
      The false expectation in this dax is as shown in this image below which is incorrectly showing true

       

      • v-sgandrathi's avatar
        v-sgandrathi
        Community Support
        1 year ago

        Hi chetan8080,

         

        Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
        Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
        Please show the expected outcome based on the sample data you provided

         

        Need help uploading data?How to provide sample data in the Power BI Forum - Microsoft Fabric Community

        Thank you.