topic Re: Trying to get this result by using DAX or Power query in DAX Commands and Tips

Trying to get this result by using DAX or Power query

YASHASHRI — Wed, 23 Nov 2022 13:12:35 GMT

Hey all,
I want to calculate the number of tenant per application.
1. I tried using power query i.e conditional column
if application contains X give X,
if application contains Y give Y,
else Others
But when i try to do that it is not giving me the accurate result
if you can see tenant id "A" Has X ,Y,Z so it should be counted in three of the results but it is just showing it in the X and Others
2. I even tried using dax

Domain =

var X= CONTAINSSTRING([Applications],"X")

var Y= CONTAINSSTRING([Applications],"Y")

var Z= CONTAINSSTRING([Applications],"Z")

var T= CONTAINSSTRING([Applications],"T")

RETURN

IF(X=TRUE,"X",

IF(Y=true,"Y",

IF(Z=true,"Z",

IF(T=true,"T", "Others"))))
Still not getting the result.

data---

Tenant ID	Application
A	X,Y,Z
B	Y,R,T
C	X,Y,T
D	R,T,X

Please give me an suggestion how can i solve this problem.

Re: Trying to get this result by using DAX or Power query

ERD — Wed, 23 Nov 2022 13:58:02 GMT

Hey YASHASHRI , you can try this measure:

tenantID# = VAR current_app = SELECTEDVALUE ( Table_2[Application] ) VAR tenantsWithApp = FILTER ( Table_, CONTAINSSTRING ( Table_[Application ], current_app ) ) RETURN IF ( ISFILTERED ( Table_2[Application] ), COUNTROWS ( tenantsWithApp ) )

Re: Trying to get this result by using DAX or Power query

YASHASHRI — Thu, 24 Nov 2022 05:24:27 GMT

what are you taking as Table_2 and Table_????

tenantID# =
VAR current_app = SELECTEDVALUE ( Table_2[Application] )
VAR tenantsWithApp = FILTER ( Table_, CONTAINSSTRING ( Table_[Application ], current_app ) )
RETURN
    IF ( ISFILTERED ( Table_2[Application] ), COUNTROWS ( tenantsWithApp ) )

Because in my case there is only one table and that is the below one

Tenant ID	Application
A	X,Y,Z
B	Y,R,T
C	X,Y,T
D	R,T,X

Re: Trying to get this result by using DAX or Power query

ERD — Thu, 24 Nov 2022 07:59:07 GMT

YASHASHRI , yes, this is the first table. And you need a table with your applications as a dimension table.