DAX measure how to add ALL, ALLEXCEPT... logic

[Anonymous]

Hi, I need some DAX help.

I created a table -> Parameter = GENERATESERIES(0, 50, 10) -> and use this in the slicer 'Control Reports'.

I create a measure to calculate the number of document per store and included the parameter value as a filter. 

Measure = 
VAR CountDocId = DISTINCTCOUNT('TEST DATA'[DOC ID])
VAR ParameterFilter = CountDocId >= Parameter[Parameter Value]
VAR Display = IF(ParameterFilter, CountDocId, BLANK())
RETURN
Display​

But is does not exactely what I want. 

If the number of documents in all the selected week columns is lower than the slicer Control Reports then don't show the store in the table (this works with my measure). 

If the number of documents in one of the selected week columns is equal or higher than the slicer Control Reports then show all the values for that store in the table (this does not work with my measure).

I suppose I need to add ALL or ALLEXCEPT or something like that. But I can't figure it out... 

Here example:

Store Ostende has more than 20 documents in several weeks. The store must be included in the table and also weeks with less than 20 docs must be displayed. That how I would like it.

Some sample data: 

DOC ID	DATE	AMOUNT	STORE ID	STORE NAME
32519945	2/01/2021	€ 214	10014082	Brussel
32522088	2/01/2021	€ 390	10014082	Brussel
32522136	2/01/2021	€ 192	10014082	Brussel
32522331	2/01/2021	€ 15	10042955	Charleroi
32524626	2/01/2021	€ 130	10046642	Lommel
32524644	2/01/2021	€ 117	10046642	Lommel
32524723	2/01/2021	€ 2.399	10014082	Brussel
32528319	3/01/2021	€ 105	10046642	Lommel
32531874	4/01/2021	€ 115	10014076	Lille
32536322	4/01/2021	€ 175	10042955	Charleroi
32536843	4/01/2021	€ 133	10046642	Lommel
32536900	4/01/2021	€ 63	10046642	Lommel
32541040	5/01/2021	€ 123	10014076	Lille
32541265	5/01/2021	€ 80	10046642	Lommel
32541738	5/01/2021	€ 44	10014076	Lille
32544172	5/01/2021	€ 74	10042955	Charleroi
32545089	5/01/2021	€ 279	10014082	Brussel

Can anyone help out?

Kind regards,
Julie 

[v-xiaotang · Accepted solution]

Try this,

create the measures,

selectedValue = SELECTEDVALUE('Control reports'[Parameter])

total_Pername_PerWeek = CALCULATE(SUM('TEST DATA'[AMOUNT]),FILTER(ALLSELECTED('TEST DATA'),'TEST DATA'[STORE NAME]=MIN('TEST DATA'[STORE NAME]) && WEEKNUM('TEST DATA'[DATE])=WEEKNUM( MIN('TEST DATA'[DATE]))))

Test = 
var _maxForEachName=MAXX(FILTER(ALLSELECTED('TEST DATA'),'TEST DATA'[STORE NAME]=MIN('TEST DATA'[STORE NAME])),[total_Pername_PerWeek])
return IF(_maxForEachName>[selectedValue],SUM('TEST DATA'[AMOUNT]))

result

Best Regards,

Community Support Team _Tang

If this post helps, please consider Accept it as the solution to help the other members find it more quickly.

[ahmedoye]

Try using this pattern below:

Counts =
VAR CountDocs =
    CALCULATE (
        DISTINCTCOUNT ( 'DataTable'[DOC ID] ),
        ALLEXCEPT ( 'DataTable', 'DataTable'[STORE NAME] )
    )
VAR CheckLimit =
    SELECTEDVALUE ( Parameter[Parameter] )
VAR SelectedStore =
    SELECTEDVALUE ( 'DataTable'[STORE NAME] )
VAR TableVisible =
    FILTER (
        ALL ( 'DataTable'[STORE NAME] ),
        'DataTable'[STORE NAME] = SelectedStore
            && CALCULATE ( CountDocs ) >= CheckLimit
    )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( 'DataTable'[DOC ID] ),
        'DataTable'[STORE NAME] IN TableVisible
    )

If this solution works for you, kindly mark as solution to allow others who may have similar challenge find it easily.

[Anonymous]

Thank you for helping out  @ahmedoye !
The measure is almost ok. The measure should filter the table based on the documents per week (not total documents on the selected period). If the number of control reports for one week is equal or above the number of control reports selected on the slicer then show the store and number of document for all the selected weeks. 

In the screenshot below is the result of your measure. 
Charleroi, Lommel and Paris should not show in the table because none of the weeks is above 18 control reports. 
Brussel and Ostende should be displayed because some weeks are above the 18 control reports. 

I tried to correct the measure but am not sure how to. 
The part in red wrongly filters the table but I don't know how to change it correctly...

Thanks for helping out!  

Greetings, Julie