cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Laxman
Frequent Visitor

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

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.

Laxman_0-1615763456874.png

 

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

 
 

Count Sale Type.JPG

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.

1 ACCEPTED SOLUTION
mahoneypat
Super User IV
Super User IV

Please try this measure expression.

 

Days with Sales = DISTINCTCOUNT(Fact_sales[DateKey])

 

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

4 REPLIES 4
mahoneypat
Super User IV
Super User IV

Please try this measure expression.

 

Days with Sales = DISTINCTCOUNT(Fact_sales[DateKey])

 

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

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
Community Champion
Community Champion

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

 

Regards
Amine Jerbi

If I answered your question, please mark this thread as accepted
and you can follow me on
My Website, LinkedIn and Facebook

Laxman
Frequent Visitor

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 

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

R2 (Green) 768 x 460px.png

Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.