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 result rest others should get 0,
These values are not unique in the table backend, these get repeated in the table, The measure should ignore the table and just check values from the visual and give results.
Basically it should check rows in the visual and those rows which have unique ID should give 1 as result or else 0
Thank you in advance

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.

8 Replies

  • SolomonovAnton's avatar
    SolomonovAnton
    Super User
    1 year ago

    To achieve a measure that evaluates uniqueness *based on what's displayed in the matrix visual* (ignoring backend duplicates), you can use the DAX function ISINSCOPE along with COUNTROWS and FILTER on visible data using ALLSELECTED.

    Here’s a measure that does this:

    Unique In Visual =
VAR CurrentValue = SELECTEDVALUE('YourTable'[ID])
VAR CountVisible = 
    CALCULATE(
        COUNTROWS('YourTable'),
        FILTER(
            ALLSELECTED('YourTable'),
            'YourTable'[ID] = CurrentValue
        )
    )
RETURN IF(CountVisible = 1, 1, 0)

    🧩 How it works:

    • SELECTEDVALUE: Gets the current row value in the matrix.
    • ALLSELECTED: Limits calculation to only what's visible in the matrix (not the whole table).
    • FILTER + COUNTROWS: Counts how many times that value appears in the visual.
    • IF = 1: Means the value appears only once = unique in visual.

    📌 Usage Tip:

    Add this measure to your matrix visual along with the ID and check the output. The measure will return 1 for unique IDs in the visual and 0 for repeated ones.

    📚 Reference:

    ✔️ If my message helped solve your issue, please mark it as Resolved!

    👍 If it was helpful, consider giving it a Kudos!

  • 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