Eliminate Records DAX

[Amitkr174]

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 ID	Milestone	Milestone Status
BG-1000	1	Completed
BG-1000	2	In progress
BG-1000	3	Canceled
BG-1000	4	Completed
BG-1000	5	In progress

[Amitkr174 · Accepted solution]

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

[v-yiruan-msft · Accepted solution]

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]

@v-yiruan-msft - This is great, this solved my purpose. Thank you very much!

[Amitkr174 · Accepted solution]

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

[VahidDM]

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]

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.

[VahidDM]

HI @Amitkr174 

Dis you use my codes as a measure or column? Can you share you PBIX file after removing sensetive data?

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]

@VahidDM  I used it as a measure. My data is connected to direct query so even if I'll upload the power bi report, you won't be able to access the dataset 😞

[smpa01]

@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] )
    )
)

[smpa01]

@Amitkr174  what is the desire doutput for the sample above?