Filtering not working properly in a calculated column

[Anonymous]

Requirement: -
I have to create a checkbox filter, to display the Client's data(SourceName field) when there is an Order Id (i.e. a transaction) for Q12020 (i.e. Jan-2020, Feb-2020, Mar-2020) from the Transaction table.  So I am trying to return either
1) True/False whenever there is an order_id for those transaction months or
2) SourceName for those transaction months.
ClientKey is the column on which both the tables (Transaction and Client) are related as Many to one.

This is what I have tried in a calculated column:

1) Flag =

var countorderid = COUNTX(FILTER(RELATEDTABLE('olap fact_Transaction'),[TransactionYearMonth]= "Jan-20" || [TransactionYearMonth]= "Feb-20" ||[TransactionYearMonth]= "Mar-20"),[order_id])

RETURN

IF(countorderid>0,"True","False")

But, it does not give correct results, like Flag should be False for Jan-2018 and so on and True only for Jan-2020, Feb-2020, Mar-2020.

I also tried doing this in a different way ,
by displaying all Clients Name (SourceName from Client Table) for the filter Q12020:

2) TESTCOLUMN =

var clientname = CALCULATETABLE(

DISTINCT('olap dim_Client'[SourceName]),

SWITCH('olap fact_Transaction'[TransactionYearMonth],

"Jan-20",TRUE(),

"Feb-20" ,TRUE(),

"Mar-20",TRUE(),

FALSE()

))

RETURN

clientname

Result:

It is not filtering properly, it displays data for all TransactionYearMonth for few clients even when the Filter condition(Q12020) is not satisfied.
Not sure what am I missing here?

[amitchandak · Accepted solution]

@Anonymous , Try like

Flag =
var countorderid = COUNTX(FILTER(RELATEDTABLE('olap fact_Transaction'),'olap fact_Transaction'[TransactionYearMonth] in{ "Jan-20" , "Feb-20","Mar-20"}),[order_id])+0
RETURN
IF(countorderid>0,"True","False")

If not

Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[Anonymous]

Hi @amitchandak ,

I have tried your way, it still does not show the correct results:

I will send you sample data as well.

[amitchandak]

@Anonymous , Try new column

if([DateSort] in {202001, 202002,202003} , 1, 0)

or

if([DateSort] in {"202001", "202002","202003"} , 1, 0)

[Anonymous]

Hi @amitchandak ,
I tried doing it and the results are as below:

The catch is this won't work in my scenario, because now If I put a filter/Slicer on this column, it will show me data for just these three months, but I want to display data for all Transaction Months but for only those Clients who have transactions in at least Jan-20, Feb-20, Mar-20 months.
For this reason, I am trying to take a count of Order ID for Q12020 and then display True/False.

[Anonymous]

The above-mentioned issue has been resolved by creating this calculated column:

Flag =

var countorderid = COUNTX(

FILTER(RELATEDTABLE('olap fact_Transaction'),

RELATED('olap dim_TransactionType'[Type]) ="Ticket" 

&& 

'olap fact_Transaction'[TransactionYearMonth] in{"Jan-20","Feb-20","Mar-20"}),

[order_id])+0

RETURN

IF(countorderid>0,"True","False")

@amitchandak ,
Thanks a lot for your help 🙌