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 colum...
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.

PattemManohar's avatar
PattemManohar
Icon for Community Champion rankCommunity 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
      Icon for Community Support rankCommunity 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.

      • Anonymous's avatar
        Anonymous
        Not applicable
        7 years ago

        PattemManohar and v-yuta-msft great thanks!!

        (i think i'm missing the accept bottun... where is it?)

        found it :robotlol:

    • PattemManohar's avatar
      PattemManohar
      Icon for Community Champion rankCommunity Champion
      7 years ago

      Anonymous  Please follow below steps:

       

      Add a new column in the source table as below. This is to flag all the states as True if it contains atleast one True value.

       

       

      CheckFlag = 
VAR _CurrState = Test191Lkp[State]
VAR _CurrPresident = Test191Lkp[President]
VAR _Result = LOOKUPVALUE(Test191Lkp[Flag],Test191Lkp[State],_CurrState,Test191Lkp[President],_CurrPresident,Test191Lkp[Flag],TRUE)
RETURN IF(ISBLANK(_Result),"F","T")

       

       

      The result will now looks like..

       

       

      Now create a new table as below which will use the above table as input.

       

      Test191Out = 
VAR _True = SUMMARIZE(FILTER(Test191Lkp,Test191Lkp[CheckFlag]="T"),Test191Lkp[President],"TrueCount",DISTINCTCOUNT(Test191Lkp[State]),"Type","True")
VAR _False = SUMMARIZE(FILTER(Test191Lkp,Test191Lkp[CheckFlag]="F"),Test191Lkp[President],"FalseCount",DISTINCTCOUNT(Test191Lkp[State]),"Type","False")
RETURN UNION(_True,_False)

      The output will be 

       

       

      Now you can see the Matrix visual to represent this data in your required fashion.