Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
gaurav1991
Regular Visitor

Need Help in DAX or Any Other way!

I have a query. Please help!

 

I have 2 columns (X and Y) with 10 rows each.

 

1. In X column top 4 rows are blank and rest 6 rows have values.

"X" Column Values: [Blank, Blank, Blank, Blank, 34,56,76,87,98,98]

 

2. In Y column top 2 rows are blank and rest of the rows have values.

"Y" Column Values: [Blank, Blank, 233, 333, 334, 456, 5765, 58, 5598, 6798]

 

Now as per the requirement, I need a calculated column Named "XYZ", 

this column needs to be populated by the count of values in each row of both columns X and Y

"XYZ" Column Values: [0, 0, 1, 1, 2, 2, 2, 2, 2, 2]

3 REPLIES 3
Phil_Seamark
Employee
Employee

Hi @gaurav1991

 

Please try this calculated column

 

XYZ = SWITCH(
            TRUE() , 
            ISBLANK('Table1'[X]) = true && ISBLANK('Table1'[Y]) = TRUE , 0 ,
            ISBLANK('Table1'[X]) = false && ISBLANK('Table1'[Y]) = FALSE , 2 ,
            --ELSE--
            1
            )

 


To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Hello Phil,

 

I can't hard code the columns as column X and Y are actually the month's name and that will varies as the new sales data will be uploaded.

 

Actual Case, Its quarterly sales data, Month's names are used in columns after pivoting the data. I just need a dynamic function in which I don't need to hardcode the column name as that would be changed in next quarter.

 

If keep the Data vertical and not pivot it if you want to do this dynamically.

To learn more about DAX visit : aka.ms/practicalDAX

Proud to be a Datanaut!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.