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
erikmi
Frequent Visitor

Calcualted Column to flag cokpleted months in fact table

I need a calcualted column that I can use as a slicer to eahter only schow revenue for completed months ore to schow all the revenue. 

 

I have following tables:

FactRevenue: ProjectID| Date| ActualRevenue|ActualProduction
Calendar: Date|Month|.....

Several Dimensions

 

The calculated column should check for ech projectID if there is a value ind the revenue AND in the production column for a given Date. If there is a value for both Production and Revneu in for a Project at the given date , the column should return "completed", otherwise it should return "incomplete"

1 ACCEPTED SOLUTION

Hi @erikmi ,

 

Please try this :

Column = 
IF('FactRevenue'[ActualProduction]<> BLANK() &&'FactRevenue'[ActualRevenue] <> BLANK(),"completed","incompleted")

vtangjiemsft_0-1678178186165.png

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

View solution in original post

4 REPLIES 4
erikmi
Frequent Visitor

I think your solution works the way I need it to, only problem is I need it to work as a Slicer, so it needs to be a calculated column.

Hi @erikmi ,

 

Please try this :

Column = 
IF('FactRevenue'[ActualProduction]<> BLANK() &&'FactRevenue'[ActualRevenue] <> BLANK(),"completed","incompleted")

vtangjiemsft_0-1678178186165.png

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

@v-tangjie-msft 
Looks good. Thank you!

 

Now I need to add a condition that checks if there is data for every Porject, menaing does every project exist in specific month 

v-tangjie-msft
Community Support
Community Support

Hi @erikmi ,

 

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

vtangjiemsft_0-1678175745682.png

(2) We can create two measures.

Measure = 
var _a =SELECTEDVALUE('CALENDAR'[Date])
return 
IF(MAX('FactRevenue'[Date])=_a && MAX('FactRevenue'[ActualProduction]) <> BLANK() && MAX('FactRevenue'[ActualRevenue]) <> BLANK(),"completed","incompleted")
Measure 2 = var _a =SELECTEDVALUE('CALENDAR'[Date])
var _b =CALCULATE(MAX('FactRevenue'[Date]),FILTER('FactRevenue','FactRevenue'[Date]))
return 
IF(_b=_a ,1,0)

(3) Then the result is as follows.

vtangjiemsft_1-1678175862057.png

 

 

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

 

Best Regards,

Neeko Tang

If this post  helps, then please consider Accept it as the solution  to help the other members find it more quickly. 

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.