Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

exists in same table

hi, i'm new to Power BI...

not sure if i need a calculated column or a measure.

I want to check for each row if another row exists where value in column A is the same as this row and value in column B is 1.

so basically I would have a new boolean column.

or, optionally to have a new table with distinct columns A, B and the new bool column.

 

how should i do that in DAX and sould it be a column or a measure?

thanks

Need Help
  • v-yuta-msft's avatar
    v-yuta-msft
    7 years ago

    Anonymous ,

     

    Create two measures using DAX below:

    count with true = CALCULATE(DISTINCTCOUNT('Table'[A]), FILTER(ALLEXCEPT('Table', 'Table'[B]), 'Table'[C] = TRUE()))
    count with false = CALCULATE(DISTINCTCOUNT('Table'[A]), FILTER(ALLEXCEPT('Table', 'Table'[B]), 'Table'[C] = FALSE()))

     

     

    Community Support Team _ Jimmy Tao

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

5 Replies

  • PattemManohar's avatar
    PattemManohar
    Community Champion
    7 years ago

    Anonymous  You can use a conditional column to flag row alreayd exists or not. But it will be great if you can post some sample data and expected output to suggest an accurate solution.

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      thank you PattemManohar !

      lets say i have the following data:  (ignore column D, it's only to show that data is distinct)

       

       

       

       

       

       

       

      (of course real data is much bigger)

      I need a report (or a graph) that will count distinct countries for each president that have at least one true value in column C.  and a count of countries without any true value.

      so the report would be like that:

       

       

       

      I guess there are a few ways to do that, but i'm not sure how...

      Thanks a lot

       

      I know my original question didn't mention all that :smileyhappy:  I was just trying to simplify

      • v-yuta-msft's avatar
        v-yuta-msft
        Community Support
        7 years ago

        Anonymous ,

         

        Create two measures using DAX below:

        count with true = CALCULATE(DISTINCTCOUNT('Table'[A]), FILTER(ALLEXCEPT('Table', 'Table'[B]), 'Table'[C] = TRUE()))
        count with false = CALCULATE(DISTINCTCOUNT('Table'[A]), FILTER(ALLEXCEPT('Table', 'Table'[B]), 'Table'[C] = FALSE()))

         

         

        Community Support Team _ Jimmy Tao

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