Hi All,

[MP275]

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. 

[MP275]

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

Thank you so much for the help

[DataInsights · Accepted solution]

@MP275,

Try a measure like this:

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

[MP275]

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)