Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Grow your Fabric skills and prepare for the DP-600 certification exam by completing the latest Microsoft Fabric challenge.

Reply
Laxman
Helper I
Helper I

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
Employee
Employee

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
Employee
Employee

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


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

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Power BI Monthly Update - May 2024

Check out the May 2024 Power BI update to learn about new features.