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
s213bell
New Member

Need Help with DAX if statment

Hello,

 

I just started using power BI and was wondering if I could get some help.

 

I have 3 tables, Current customers, Our Historic List and a third table that needs to be filled.

 

The third table will look at the id in the current table, see if it is in the historic table and if not add that id to the third table.

New customer = if('Current'[ID] = RELATED('Historic'[id]),nothing,'current'[student id number])
 
First off I am not sure if this is the right way to do it. Basically I want it to count all the new ID's and give us a list.
 
But it also says it can't even find the ID column in the current table and I checked the spelling. Any suggestions would be appreciated.
 
Thank you
 

 

1 ACCEPTED SOLUTION

@s213bell 

create a calculated column in current customers table to identify id present in historic customers table.

present historic id = LOOKUPVALUE(Historic List[Customer ID historic],Historic List[Customer ID historic],Current customers[Customer ID current])
This above dax will give you a column that displays customer id present in historic customer table and new customer id as blank.
Then create a measure to count blank rows as follows,
COUNTBLANK(Current customers[present historic id])
Total blank rows in column 'present historic id' will give you total no.of. new customer ids.
 
If this post helps you to resolve your problem, then mark it as 'Accept as solution' below.

View solution in original post

4 REPLIES 4
RohiniP-26
Resolver I
Resolver I

Post some sample data for clear understanding

@RohiniP-26 

Hello,

 

So I have a table with a column Customer ID for our historic list like below

Customer ID Historic
1
2
3
4
5
6
7

8

 

Then we have say the weekly report that has the same column

Customer ID Current
5
2
9
65
2

 

I want to take this column from this table and say if the id in this table is also in the Historic table customer ID ignore.  If not print that ID in the third table.  This is so we can count all the new id's for that month.  Let me know if you need any more information

@s213bell 

create a calculated column in current customers table to identify id present in historic customers table.

present historic id = LOOKUPVALUE(Historic List[Customer ID historic],Historic List[Customer ID historic],Current customers[Customer ID current])
This above dax will give you a column that displays customer id present in historic customer table and new customer id as blank.
Then create a measure to count blank rows as follows,
COUNTBLANK(Current customers[present historic id])
Total blank rows in column 'present historic id' will give you total no.of. new customer ids.
 
If this post helps you to resolve your problem, then mark it as 'Accept as solution' below.

Thank you so much that helped.

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.