topic Re: Tretas : Not getting count based on variable in DAX Commands and Tips

Tretas : Not getting count based on variable

Chandrashekar — Fri, 14 Jan 2022 10:23:52 GMT

Hello Team,

am trying to get count of priority am using below code but it is not working.

Inflow_Priority =

var CompanyRegionAU_Company =

Max(CompanyRegionAU[Company])

Var CompanyRegionAU_Region=

Max(CompanyRegionAU[Region])

Var queueselect_queue=

Max(Queue[Queue])

Var Table_join=

CROSSJOIN(

SELECTCOLUMNS({CompanyRegionAU_Company},"Name",[Value]),

SELECTCOLUMNS({CompanyRegionAU_Region},"Region",[Value]),

SELECTCOLUMNS({queueselect_queue},"Queue",[Value])

)

RETURN

CALCULATE(

COUNTROWS(Source),FILTER(all(Source),Source[Priority_is]=1),

TREATAS(Table_join,Source[company],Source[Region],Source[Queue])

)

Regards,

Chandrashekar B

Re: Tretas : Not getting count based on variable

Chandrashekar — Fri, 14 Jan 2022 10:49:39 GMT

Hello Team,

Please see attached example report Sample Report as am trying to get priority count am using below code but it is not working.

Re: Tretas : Not getting count based on variable

DimaMD — Fri, 14 Jan 2022 13:02:59 GMT

Hi, Is this your desired result?

Re: Tretas : Not getting count based on variable

smpa01 — Fri, 14 Jan 2022 14:27:44 GMT

Chandrashekar I am guessing you are expecting this

To reach the following outcome, you need two measures

_countrows = VAR _name = MAX ( Table3[Name] ) VAR _location = MAX ( Table3[Location] ) VAR _tbl = CROSSJOIN ( SELECTCOLUMNS ( { _name }, "name", [Value] ), SELECTCOLUMNS ( { _location }, "location", [Value] ) ) RETURN CALCULATE ( COUNTROWS ( FILTER ( Table1, Table1[Priority] = 1 ) ), TREATAS ( _tbl, Table1[Name], Table1[Location] ) ) Sum = SUMX(Table3,[_countrows])

Re: Tretas : Not getting count based on variable

AlexisOlson — Fri, 14 Jan 2022 16:02:54 GMT

The cross join construction feels odd to me.

I think I'd try writing this DAX more like this:

Inflow_Priority = CALCULATE ( COUNTROWS ( Source ), FILTER ( ALL ( Source ), Source[Priority_is] = 1 ), TREATAS ( SUMMARIZE ( CompanyRegionAU, CompanyRegionAU[Company], CompanyRegionAU[Region] ), Source[company], Source[Region] ), TREATAS ( VALUES ( Queue[Queue] ), Source[Queue] ) )

Re: Tretas : Not getting count based on variable

Chandrashekar — Sat, 15 Jan 2022 09:08:25 GMT

Hello,

I need to Priority1 and Pririty 2 in two different columns.

Regards,

Chandrashekar B

Re: Tretas : Not getting count based on variable

Chandrashekar — Sat, 15 Jan 2022 09:16:54 GMT

Hello,

I need to Priority1 and Pririty 2 in two different columns.

Regards,

Chandrashekar B

Re: Tretas : Not getting count based on variable

Chandrashekar — Sat, 15 Jan 2022 09:25:53 GMT

Hello Alex,

I tried your solution but it is not working. I need Priority 1 and Priority 2 in different columns.

Regards,

Chandrashekar B

Re: Tretas : Not getting count based on variable

Chandrashekar — Sat, 15 Jan 2022 10:51:26 GMT

Hello Alex,

Sorry it is my mistake(Format issue it was text and turned to Number) and it is working now.

Re: Tretas : Not getting count based on variable

AlexisOlson — Sat, 15 Jan 2022 16:31:00 GMT

Great!

Please accept the solution(s) that resolved your question in order to mark it as accepted so future readers can find it more quickly.