Forum Discussion

Amitkr174's avatar
Amitkr174
Icon for Helper III rankHelper III
4 years ago
Solved

Eliminate Records DAX

Hi,

I need your help in DAX. I have a scenarion that a Budget has many Milestones with different status.

I need to eliminate those budgets all together where the Milestone status is cancelled.   

Below is the table for your reference:- 

 

Budget IDMilestoneMilestone Status
BG-10001Completed
BG-10002In progress
BG-10003Canceled
BG-10004Completed
BG-10005In progress
  • Amitkr174's avatar
    Amitkr174
    4 years ago

    smpa01  Desired output would be that this record (Budget id) will not appear in the report.

  • Anonymous's avatar
    Anonymous
    4 years ago

    Hi Amitkr174 ,

    I created a sample pbix file(see attachment), please check whether that is what you want.

    1. Create a measure as below to judge whether the current budget include cancelled status

    Flag = 
VAR _selbuget =
    SELECTEDVALUE ( 'Table'[Budget ID] )
VAR _tab =
    CALCULATETABLE (
        VALUES ( 'Table'[Budget ID] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Milestone Status] = "Canceled" )
    )
RETURN
    IF ( _selbuget IN _tab, 0, 1 )

    2. Create a visual and apply a visual-level filter with condition (Flag is 1)

    If the above one can't help you get the expected result, please provide more sample data with Text format and your expected result with calculation logic and special examples. Thank you.

    Best Regards

9 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi Amitkr174 ,

    I created a sample pbix file(see attachment), please check whether that is what you want.

    1. Create a measure as below to judge whether the current budget include cancelled status

    Flag = 
VAR _selbuget =
    SELECTEDVALUE ( 'Table'[Budget ID] )
VAR _tab =
    CALCULATETABLE (
        VALUES ( 'Table'[Budget ID] ),
        FILTER ( ALLSELECTED ( 'Table' ), 'Table'[Milestone Status] = "Canceled" )
    )
RETURN
    IF ( _selbuget IN _tab, 0, 1 )

    2. Create a visual and apply a visual-level filter with condition (Flag is 1)

    If the above one can't help you get the expected result, please provide more sample data with Text format and your expected result with calculation logic and special examples. Thank you.

    Best Regards

    • Amitkr174's avatar
      Amitkr174
      Icon for Helper III rankHelper III
      4 years ago

      Anonymous - This is great, this solved my purpose. Thank you very much!

  • Amitkr174's avatar
    Amitkr174
    Icon for Helper III rankHelper III
    4 years ago

    smpa01  Desired output would be that this record (Budget id) will not appear in the report.

    • smpa01's avatar
      smpa01
      Icon for Community Champion rankCommunity Champion
      4 years ago

      Amitkr174  achievable in two ways

      _m1 = 
VAR _0 =
    ALLEXCEPT ( tbl, tbl[Budget ID] )
VAR _1 =
    SUMMARIZE (
        CALCULATETABLE ( tbl, tbl[Milestone Status] IN { "Cancelled" }, _0 ),
        tbl[Budget ID]
    )
VAR _2 =
    SUMMARIZE (
        CALCULATETABLE ( tbl, NOT tbl[Milestone Status] IN { "Cancelled" }, _0 ),
        tbl[Budget ID]
    )
VAR _3 =
    EXCEPT ( _2, _1 )
RETURN
    CALCULATE ( MAX ( tbl[Milestone Status] ), TREATAS ( _3, tbl[Budget ID] ) )

       

      _m2 = 
MAXX (
    tbl,
    IF (
        ISEMPTY (
            CALCULATETABLE (
                tbl,
                tbl[Milestone Status] IN { "Cancelled" },
                ALLEXCEPT ( tbl, tbl[Budget ID] )
            )
        ),
        MAX ( tbl[Milestone Status] )
    )
)

       

       

    • VahidDM's avatar
      VahidDM
      Icon for Super User rankSuper User
      4 years ago

      Hi Amitkr174 

       

      Try this Measure:

      Measure = 
Var _A = CALCULATE(COUNTROWS('Table'),filter(ALLEXCEPT('Table','Table'[Budget ID]),'Table'[Milestone Status]="Canceled"))
return
iF(_A>0,BLANK(),1)

       

      and add that to the filter of your visual and set the filter to shows 1.

       

      Output:

       

       

      If you want to add a new colum, use this code:

      Column = 
Var _A = CALCULATE(COUNTROWS('Table'),filter(ALLEXCEPT('Table','Table'[Budget ID]),'Table'[Milestone Status]="Canceled"))
return
if(_A>0,BLANK(),[Milestone Status])

       

      output:

       

       

      If this post helps, please consider accepting it as the solution to help the other members find it more quickly.
      Appreciate your Kudos!!
      LinkedIn:       www.linkedin.com/in/vahid-dm/

       

       

      • Amitkr174's avatar
        Amitkr174
        Icon for Helper III rankHelper III
        4 years ago

        VahidDM - I tried but still the Budget which has canceled Milestone is appearing, even if I set the filter to 1 budget id will that has canceled Milestone will appear.