Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Country | Sales | Rank | Continent | Sl |
India | 100 | 3 | Asia | 1 |
Pakistan | 50 | 3 | Asia | 2 |
France | 95 | 1 | Europe | 4 |
China | 90 | 3 | Asia | 3 |
India | 89 | 4 | NULL | 1 |
I have a Table A as shown above, and Table B as below:
Country | Sl | Sales |
India | 1 | #ERROR |
Pakistan | 2 | #ERROR |
China | 3 | #ERROR |
Im looking up Sales from Table A in Table B, However I want to lookup Table A excluding Continent = NULL,
How to frame the DAX for lookup by excluding the mentioned row from table A.
Solved! Go to Solution.
Hi @Antmkjr,
You could change the data type of the Measure by Column tools.
Or make a little bit change to the Measure itself.
LookUpSales =
var res=SUMX (
FILTER (
'Table A',
'Table A'[Country] = 'Table B'[Country]
&& 'Table A'[Continent] <> "NULL"
&& 'Table A'[Sl] = 'Table B'[Sl]
),
'Table A'[Sales]
)
return CONVERT(res,STRING)
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please let me know. Thanks a lot!
Best Regards,
Community Support Team _ Caiyun
Hi @Antmkjr,
You need make some changes to your Calculated column formula.
LookUpSales =
SUMX (
FILTER (
'Table A',
'Table A'[Country] = 'Table B'[Country]
&& 'Table A'[Continent] <> "NULL"
&& 'Table A'[Sl] = 'Table B'[Sl]
),
'Table A'[Sales]
)
Then, the result looks like this.
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please let me know. Thanks a lot!
Best Regards,
Community Support Team _ Caiyun
In this case its fine, but what if the value i have to return is text
Hi @Antmkjr,
You could change the data type of the Measure by Column tools.
Or make a little bit change to the Measure itself.
LookUpSales =
var res=SUMX (
FILTER (
'Table A',
'Table A'[Country] = 'Table B'[Country]
&& 'Table A'[Continent] <> "NULL"
&& 'Table A'[Sl] = 'Table B'[Sl]
),
'Table A'[Sales]
)
return CONVERT(res,STRING)
If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please let me know. Thanks a lot!
Best Regards,
Community Support Team _ Caiyun
@Antmkjr , a New column in Table B
Sumx(filter( 'Table A', 'Table A'[Country] = 'Table b'[Country] ), 'Table A'[Sales])
or
Sumx(filter( 'Table A', 'Table A'[Country] = 'Table b'[Country] && 'Table A'[sl] = 'Table b'[sl] ), 'Table A'[Sales])
In this case of both of these its Summing up the sales of India where Continent = NULL , i want to completely ignore that record
User | Count |
---|---|
59 | |
22 | |
18 | |
18 | |
16 |
User | Count |
---|---|
87 | |
59 | |
52 | |
37 | |
21 |