Forum Discussion
DAX-help.
Hi,
Require help in acheving the below count of reference number, sample data set shared below.
Similar to below syntax in DAX.
if (Month=Jan, logic=0,1,2 Result[Count of Reference Numbers that are in(Logic 0,1,2)]
elseif Month=Feb,Logic=3,6,7 Result[Count of Reference Numbers that are in(Logic 5,6,7)]
elseif Month=March,Logic=5,8,9 Result[Count of Reference Numbers that are in(Logic 5,6,7)]
.......
),Blank())
Regards,
Sam
Hi Anonymous ,
Here we go!
Column = VAR from1_2 = CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', 'Table'[Logic] IN { 0, 1, 2 } && 'Table'[Year] = EARLIER ( 'Table'[Year] ) ) ) VAR from3_7 = CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', 'Table'[Logic] IN { 3, 6, 7 } && 'Table'[Year] = EARLIER ( 'Table'[Year] ) ) ) VAR from5_9 = CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', 'Table'[Logic] IN { 5, 8, 9 } && 'Table'[Year] = EARLIER ( 'Table'[Year] ) ) ) RETURN SWITCH ( TRUE (), 'Table'[Month] = "Jan" && 'Table'[Logic] IN { 0, 1, 2 }, from1_2, 'Table'[Month] = "Feb" && 'Table'[Logic] IN { 3, 6, 7 }, from3_7, 'Table'[Month] = "Mar" && 'Table'[Logic] IN { 5, 8, 9 }, from5_9 )
5 Replies
Create a new column like, and then do count in visual
if (table[Month]="Jan" && table[logic] in {0,1,2} , table[Reference Numbers], if (table[Month]="Feb" && table[logic] in {3,4,5} , table[Reference Numbers] ),Blank())Try using SWITCH : https://community.powerbi.com/t5/Desktop/Help-with-SWITCH-TRUE-with-multiple-items-to-evaluate/td-p/439241
Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution. In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blog -
Winner-Topper-on-Map-How-to-Color-States-on-a-Map-with-Winners , HR-Analytics-Active-Employee-Hire-and-Termination-trend
Power-BI-Working-with-Non-Standard-Time-Periods And Comparing-Data-Across-Date-Ranges
Connect on LinkedinHi Anonymous ,
Please create a calculated column like that to work on it.
Column = SWITCH ( TRUE (), 'Table'[Month] = "Jan" && 'Table'[Logic] IN { 0, 1, 2 }, CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', 'Table'[Logic] IN { 0, 1, 2 } ) ), 'Table'[Month] = "Feb" && 'Table'[Logic] IN { 3, 6, 7 }, CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', 'Table'[Logic] IN { 3, 6, 7 } ) ), 'Table'[Month] = "Mar" && 'Table'[Logic] IN { 5, 8, 9 }, CALCULATE ( COUNTROWS ( 'Table' ), FILTER ( 'Table', 'Table'[Logic] IN { 5, 8, 9 } ) ) )Pbix as attached.
Hi Frank,
Appreciate you Prompt efforts,resolved major part of my requirement.
i have a few vales in Table [logic] colums like -1,-2,-3....... > i tried by changing data type but getting blank colums.
Planing to add a year coloum so can we can filter based on year
Can you please help me with the DAX syntax for the same.
Regards,
Santosh
