topic Re: DAX sub query with condition in DAX Commands and Tips

DAX sub query with condition

Salva_BI_DAX — Mon, 14 Nov 2022 14:10:29 GMT

Hi everyone,

I have been struggle for a while for a subquery in DAX. I have the following table

ID	Country	Name	Period
ID1	US	A	201412
ID1	US	A	201506
ID1	US	A	201512
ID10	CA	B	201412
ID10	CA	B	201506
ID10	CA	B	201512
ID10	CA	BB	201606
ID10	CA	BB	201612
ID10	CA	BB	201706
ID10	CA	BB	201712
ID10	CA	BB	201806
ID10	CA	BB	201809
ID10	CA	BB	201812
ID10	MX	C	201712
ID10	MX	C	201806
ID10	MX	CC	201809
ID10	MX	CC	201812
ID100	IE	D	201412
ID100	IE	D	201506
ID100	IE	D	201512
ID100	IE	DD	201606
ID100	IE	DD	201612
ID100	IE	DD	201706
ID100	IE	DD	201712
ID100	IE	DD	201806
ID100	IE	DD	201809
ID100	IE	DD	201812

The user has one slicer, the period and can select start and end perido (for instance from 201412 - 201612). Once, the period is selected, I need to create a report that show a list of a unique items (Name). There are few condition:

if I select from 201412 - 201612 I need to see only one "Name" that belong to to that periods (201412, 201506, 201512, 201606, 201612)
also the Name could changes in the period that I selected. For instance, ID10 will show BB and ID100 DD

This means that the output looks like:

_ID	_Country	_Name
ID10	CA	BB
ID100	IE	DD

I have tried to write a DAX but it is not working properly

Output =

var _min = CALCULATE(Min(Periods[Period]),ALLEXCEPT(Periods,Periods[Period])) // this return the min period

var _max = CALCULATE(Max(Periods[Period]),ALLEXCEPT(Periods,Periods[Period])) // this return the max period

// this return the number of periods that the Name belogs (I dont know when to use it)

var _NumOfPeriod= COUNTX(CALCULATETABLE(

Input,

FILTER(Input, Input[Period] >= Valmin && Input[Period] <= Valmax)

), Input[id])

// this should return the output

return

SELECTCOLUMNS(

FILTER(Input, [Period] >= valmin && [Period] <= valmax),

"_ID", [ID],

"_Country", [Country],

"Name", LOOKUPVALUE(_NameTable[name],_NameTable[key],CONCATENATE(Valmax,[id])) // I have created another table with a list of Name (see below)

)

-----

_NameTable =

// this table has a relation with Periods so he will show the periods selected

return

SUMMARIZE (

Input,

Input[id],

Input[name],

Input[Period],

"Key", CONCATENATE([Period], [id])

)

If someone could help me on this, it will be greatly appreciated.

Many thanks,

Salva

Re: DAX sub query with condition

v-yueyunzh-msft — Tue, 15 Nov 2022 02:11:45 GMT

Hi , Salva_BI_DAX

Here are the steps you can refer to :
(1)My test data is the same as yours.

(2)We can create a measure to filter our visual :

Display Flag = var _slicer_min =CALCULATE( MIN('Table'[Period]),ALLSELECTED('Table')) var _slicer_max=CALCULATE( MAX('Table'[Period]),ALLSELECTED('Table')) var _slicer_count =COUNTROWS( DISTINCT( SELECTCOLUMNS( FILTER(ALL('Table') , 'Table'[Period]>=_slicer_min && 'Table'[Period] <=_slicer_max),"Period",[Period]))) var _current_id =SELECTEDVALUE('Table'[ID]) var _id_count =COUNTROWS( FILTER('Table', 'Table'[ID]=_current_id)) return if(_id_count= _slicer_count ,1,0)

(3)Then we need to create another measure :

Name2 = var _max = MAX('Table'[Period]) var _current_id = SELECTEDVALUE('Table'[ID]) var _t =FILTER('Table', 'Table'[ID]=_current_id && 'Table'[Period]=_max) return MAXX(_t ,[Name])

(4)Then we need to put the [Display Flag] measure on the "Filter on this visual" and the [ID],[Country],[Name2] on the visual , the result is as follows:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

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

Re: DAX sub query with condition

Salva_BI_DAX — Tue, 15 Nov 2022 16:02:50 GMT

Hello, v-yueyunzh-msft

This is exactly what I was looking for 😀

Indeed, I saw in another form that the best approch is to create the measures instead to create subtable.

Many thanks