cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## Hi All,

I am struggling to get the below solved using dax, could someone help plss

We have date and time dimensions with sales for every 15 minute intervals. I have a measure for sum of sales, I would like to know the count of sales greater than 40,000 for a given date and area. (example a date has 10 sales where its greater than 40,000 at different intervals on that day)

I have tried the COUNTROWS but it is only counting for distinct dates.

1 ACCEPTED SOLUTION
Super User

Try a measure like this:

Count Sales Over 40k =
VAR vSalesByDateAndArea =
SUMMARIZE ( FactTable, FactTable[Date], FactTable[Area] ),
"@Sales", [Sum of Sales]
)
VAR vResult =
COUNTROWS ( FILTER ( vSalesByDateAndArea, [@Sales] > 40000 ) )
RETURN
vResult

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

3 REPLIES 3
Frequent Visitor

Hi @DataInsights, I have unioined the both fact table rows and getting the desired results.

Thank you so much for the help

Super User

Try a measure like this:

Count Sales Over 40k =
VAR vSalesByDateAndArea =
SUMMARIZE ( FactTable, FactTable[Date], FactTable[Area] ),
"@Sales", [Sum of Sales]
)
VAR vResult =
COUNTROWS ( FILTER ( vSalesByDateAndArea, [@Sales] > 40000 ) )
RETURN
vResult

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Frequent Visitor

Thanks alot @DataInsights , this is working but it doesn't calculate for some of the dates as those sales for the dates are coming from a different fact table (based on a condition, if the sales from fact 1 is 0 then use sales from fact 2), I just realised that there are two fact tables. is there a way for this?

(we are using a live model with limited access to the model).

So the requirement is to check if there are any sales more than 40000 and if not then check in fact table 2 for that date / time / region, if there is then use the sales from facttable2. and count the rows. The existing measure for sum of sales captures the same logic (if sales blank in fact 1 then use values in fact 2)

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

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

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Top Solution Authors
Top Kudoed Authors