Slicing table output

[alexumagba]

I current have this table that I am working with.

 I want to write a DAX function to display the table below where the following rules is applied

1. Show name of account opened in the 2020 to 2022 and where subscription year is 2022

2. Don't show name of account that subscription year is not 2022

3. Show name of account open in 2022 and subscription year is blank.

4. Don't show account open before 2022 and subscription year is blank

[v-yangliu-msft · Accepted solution]

Hi  @alexumagba ,

Here are the steps you can follow：

1. Create measure.

Flag =
SWITCH(
    TRUE(),
YEAR(MAX('Table'[Year opened])) >=2022&&MAX('Table'[subscription year])=BLANK(),1,
YEAR(MAX('Table'[Year opened])) >=2020&&
YEAR(MAX('Table'[Year opened]))<=2022 &&
YEAR(MAX('Table'[subscription year]))=2022,1,
YEAR(MAX('Table'[subscription year]))=2022,1,0)

2. Place [Flag]in Filters, set is=1, apply filter.

3. Result:

Best Regards,

Liu Yang

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

[v-yangliu-msft · Accepted solution]

Hi  @alexumagba ,

Here are the steps you can follow：

1. Create measure.

Flag =
SWITCH(
    TRUE(),
YEAR(MAX('Table'[Year opened])) >=2022&&MAX('Table'[subscription year])=BLANK(),1,
YEAR(MAX('Table'[Year opened])) >=2020&&
YEAR(MAX('Table'[Year opened]))<=2022 &&
YEAR(MAX('Table'[subscription year]))=2022,1,
YEAR(MAX('Table'[subscription year]))=2022,1,0)

2. Place [Flag]in Filters, set is=1, apply filter.

3. Result:

Best Regards,

Liu Yang

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

[alexumagba · Accepted solution]

Thanks. This works perfectly well

[Anonymous]

I would try Summarize and Filter for the conditions you have listed

[djurecicK2]

@alexumagba Ok. Seems like you could do this in Power Query Editor.

[djurecicK2]

Hi @alexumagba ,

 Is there a reason why you want to do this in DAX instead of using filters on the table?

[alexumagba]

Please disregard the filter. This is suppose to be a daily automated task. I want to avoid using the filter everyday