Forum Discussion

BigBadBob's avatar
BigBadBob
Icon for Helper I rankHelper I
6 years ago
Solved

Dax Measure

Hi,

 

I know im being stupid but this is driving me crazy. 

 

Based on the below data i would like to retrieve all lines where one of the lines has "Flag" = 1. I have included what the results should look like below.

 

Example Data

 

IDFlagCityDateAmount
10Timbuktu15/12/2019499
10Timbuktu15/12/2019162
10Timbuktu15/12/2019306
20Dubai15/12/2019107
20Dubai15/12/2019370
21Vatican City20/12/2019358
80Dubai15/12/201946
80Dubai15/12/201945
80Dubai15/12/2019144
80Dubai15/12/2019914
81Dubai15/12/2019817
80Dubai15/12/2019737

 

Required Result

 

IDFlagCityDateAmount
20Dubai15/12/2019107
20Dubai15/12/2019370
21Vatican City20/12/2019358
80Dubai15/12/201946
80Dubai15/12/201945
80Dubai15/12/2019144
80Dubai15/12/2019914
81Dubai15/12/2019817
80Dubai15/12/2019737

 

Big thanks in Advance!

DAX
  • AlB's avatar
    AlB
    6 years ago

    Hi BigBadBob 

    Create a calculated column that will say whether the row is to be shown:

    ShowLine = 
VAR res_ = CALCULATE(SUM(Table1[Flag]);ALLEXCEPT(Table1;Table1[ID]))
RETURN
IF ( res_ > 0; "YES"; "NO")

    Then place all the fields you want to show in a  Table visual and filter for ShowLine = "YES"

    It can also be done with a measure for the filtering, instead of a calculated column

    Please mark the question solved when done and consider giving kudos if posts are helpful.

     Cheers 

     

     

     

  • Anonymous's avatar
    Anonymous
    6 years ago

    Hi BigBadBob ,

     

    Please share sample data for both the tables and the relationship between them.

     

     

    Regards,

    Harsh Nathani

5 Replies

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

    Hi BigBadBob 

    Create a calculated column that will say whether the row is to be shown:

    ShowLine = 
VAR res_ = CALCULATE(SUM(Table1[Flag]);ALLEXCEPT(Table1;Table1[ID]))
RETURN
IF ( res_ > 0; "YES"; "NO")

    Then place all the fields you want to show in a  Table visual and filter for ShowLine = "YES"

    It can also be done with a measure for the filtering, instead of a calculated column

    Please mark the question solved when done and consider giving kudos if posts are helpful.

     Cheers 

     

     

     

    • BigBadBob's avatar
      BigBadBob
      Icon for Helper I rankHelper I
      6 years ago

      Hi - Massive thanks. The flag number is in another table. I tried using the below as a measure and it has not filtered it correctly. Sorry for not mentioning the second table in the original post

       

      Includes Cancelled =
      Var res_=
      CALCULATE(SUM(Policy[flag Number]),
      ALLEXCEPT(Transactions,Transactions[User ID]))

      RETURN
      IF(res_>=1,"Y","N")
      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Hi BigBadBob ,

         

        Please share sample data for both the tables and the relationship between them.

         

         

        Regards,

        Harsh Nathani

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi BigBadBob ,

     

    To Display =

    var __a = CALCULATE(SUM(Table3[Flag]), ALLEXCEPT(Table3,Table3[ID]))
    return
    IF (__a >=1 ,"Yes",Blank())
     
     
     
    Regards,
    Harsh Nathani
     
    Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!!