Filter columns in a calculated column correctly

[Anonymous]

The below Calculated Column does not return correct results :
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")

I tried creating the above logic in a measure, which shows me the correct count against my filter for Q12020

FlagMeasure = CALCULATE(COUNTX(RELATEDTABLE('olap fact_Transaction'),'olap fact_Transaction'[order_id]),

FILTER('olap fact_Transaction','olap fact_Transaction'[TransactionYearMonth]= "Jan-20"|| 'olap fact_Transaction'[TransactionYearMonth]= "Feb-20" ||'olap fact_Transaction'[TransactionYearMonth]= "Mar-20"))

I need to correct the calculated column since I further need to put a slicer on this column, Can someone pls help?

[v-janeyg-msft]

Hi, @Anonymous 

Has your problem been solved?

I saw that there are already many complete calculation columns in your table, and your needs are actually very simple. I think it's easy to do. But I don’t know which table you need to put your calculation column in, and what form of visual you need to present at the end, and which columns in the different tables are included, because the picture in your file is different from the picture you just showed at the beginning.

Can you show the desired result you want in the end? So we can help you soon.

Best Regards

Janey Guo

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

[Anonymous · Accepted solution]

Hi @sayaliredij, @v-janeyg-msft ,

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")

Thanks a lot for your help 

[v-janeyg-msft]

Hi, @Anonymous 

It seems that the error is caused by the problem of counting the blank value. I'm glad you can find it.

Generally, the isblank() function can also be used instead of countx(0 in order to avoid this kind of error.

You can mark your answer as the solution.

Best Regards

Janey Guo

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

[sayaliredij]

You can solve this using following steps

1. You need a table which contains all the months. 

2. You can create calculated table using following formule

MonthOrder =

    SUMMARIZECOLUMNS(

        'Month'[Month],

        "Is Order Present",

        IF(ISBLANK(COUNT(Data[Order])),FALSE,TRUE)

    )   

3. Then make relations in the tables as follows

Regards,

Sayali

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

[Anonymous]

Hi @sayaliredij ,
I have tried your way, pls see the screenshot below:

Can we filter out Is Order Present to be True only for Jan-20, Feb-20, Mar-20?
Right now, it's showing True whenever we have an order id for each month.

[sayaliredij]

Is this table your Month Table? Month table should have all possible months (even for which Orders are not present). This table seems to have duplicated columns. If you can share your pbix file (removing sensitive data), it would great to check

Regards,

Sayali

[Anonymous]

Hi @sayaliredij ,
yes, this is a Date Table that has a Month Number column. I am not sure what you mean, but it has all 12 months.

This is how the relationship is:

"Date" table is joined to my Fact "Transaction" table on "DateKey" and "MonthOrder" Table is joined to Date table on MonthNumber.
and this is how the results are:-

so there are order ids for all the years and months (2018,2019,2020,2021), but I just want to display "True" for TransactionMonth = Jan-20, Feb-20, Mar-20

Please let me know where can I share the .pbix file as well (it does not allow me to attach here)

[sayaliredij]

Can you upload it on One-drive or Dropbox? 

Regards,

Sayali

[Anonymous]

Hi @sayaliredij,

Attached the link 
https://drive.google.com/file/d/1NU4yjXJ5nMPVa5ltBMRf8ULWU0BXSpiK/view?usp=sharing

Just wanted to let you know, I have created a "Flag" column which shows results "True" almost rightly for Clients ("SourceName") who have transactions in Q12020, but it's not working for few clients as shown below:

[sayaliredij]

Check the attached PBIX file

https://www.dropbox.com/s/2xuire1av3jlfu7/Health%20Dashboard%20-%20for%20Testing%20Month%20Table.pbi...

i understood that you already have date table so we don't need special month table. 

now I am getting the following result

[sayaliredij]

Can you explain a little bit more? What I understood - you want to create true value when there are orders in the month

and false ->when the order count for the month is 0

Is this correct?

Regards,

sayali

[Anonymous]

Yes, basically I have to create a checkbox filter, to display data when there is an Order Id (i.e. a transaction) for Q12020 (i.e. Jan-2020, Feb-2020, Mar-2020).  So I am trying to return True/False whenever there is an order_id for those transaction months.
I am actually not sure which function to use to create a calculated column that results correctly: 

Flag =

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

RETURN

countorderid

If you see countorderid is not coming correctly, firstly this value 40026 is not correct and secondly, it is displaying for all TransactionYearMonth and not just for Q12020