DAX Measure to find "on how many days a particular field value appeared in the selected time period"

[Laxman]

Hi,

I'm in a tricky situation where I'm trying to find "on how many days a particular sale type appeared in the selected time period".

Report uses 3 fields.

Dim_Date[DateAlternateKey] -> Connected to Fact_Sales with DateKey

Dim_Item[Store Item Id] -> Connected to Fact_Sales with ItemIdentificationKey
Fact_Sales[Sale Type] -> A field value where the particular item is sold on Normal sale / Special 1 / Special 2 / Special 3 / Markdown sale.

An item with Store Item Id = 09300617065876 is sold in the store on below days in "December 2020" on the mentioned Sale Type.

Now I want to find out the 3rd column below which is mentioned in the subject.

I tried below measures but it did not work.

Count := CALCULATE(DISTINCTCOUNT(Fact_Sales[Sale Type]);

Count := CALCULATE(COUNT(Fact_Sales[Sale Type])

Could you please help me in correcting the measure.

Thank you in advance.

[mahoneypat · Accepted solution]

Please try this measure expression.

Days with Sales = DISTINCTCOUNT(Fact_sales[DateKey])

Regards,

Pat

[Laxman]

Thank you very much Pat.

It appears to be working.

Now, I'm heading to actual business problem i.e. on how many days, the item was on the "sale type of last sold" in the selected time period".

In this case, I'm trying to check the result with hard-coded Sale Type 

CALCULATE(CALCULATE(DISTINCTCOUNT(Dim_Date[DateKey]), Fact_Sales),FILTER (ALL(Fact_Sales[Sale Type]),Fact_Sales[Sale Type] = "Special 3"))

The above formula seems to be working. 

But it would be great if you could help me with a better way of wiriting it.

Thank you in advance.

[aj1973]

Hi @Laxman 

When using CALCULATE you need an expression and a filter.

 Example:

Count := CALCULATE(COUNTROWS(Fact_Sales[Sale Type]) , Filter('Fact_Sales',Fact_Sales[Sale Type]="Special 2"))

[Laxman]

Thank you AJ.

I tried the above formula. But it shows below error

"Semantic Error: A single value for column 'Sale Type' in table 'Fact_Sales' cannot be determined. This can happen when a measure formula refers to a column that contains many values without specifying an aggregation such as min, max, count, or sum to get a single result."

It could be because on a particular day, the item was sold in multiple transactions on the specified Sale Type.

Could you please help me know if there is a way to fix it.

Thanks in advance