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
Anonymous
Not applicable

Identify the missing sections in a table

Here's the sample model that helps to identify the missing sections on my real time scenario.

There is a table which has different sections - I am extracting only the July, Aug, Sep months of data that has missed a section in a month. 

 

IDSubYearMonth
1Section12022Jul
1Section12022Aug
1Section12022Sep
1Section12022Oct
2Section22022Jul
2Section22022Oct
3Section32022Aug
3Section32022Sep
3Section32022Oct

 

Expected output: ( Only these values has to present as table in report )

2Section22022Aug
2Section22022Sep
3Section32022Jul

 

Kindly suggest any possibilities to resolve it in power bi, Thanks in advance!

1 ACCEPTED SOLUTION
daXtreme
Solution Sage
Solution Sage

Here you are. File attached.

View solution in original post

4 REPLIES 4
daXtreme
Solution Sage
Solution Sage

Here you are. File attached.

Anonymous
Not applicable

@daXtreme  I need one more hint/support in this method. In advance you stated 2023 data as well for missing values in "T_FullMonths". In visual at 'Absent' checkbox it lised 2023 and sections. Rather than listing all Year and Month in advance. Is there any possibilities that limited it to report only the existing months listed in "T" table.  In other terms, If I include Nov 2022 data sections in "T" table (data source) for this month, I need to manully update the 2022, Nov in "T_FullMonths" table. Just looking for an alternate in that particular method. Any guidance will be appreciated.

daXtreme
Solution Sage
Solution Sage

Hi @Anonymous 

 

The best and easiest way to do it is to... identify the missing pieces in Power Query (should not be hard and definitely easier than in DAX!) and create a table with them in there. Then import this table into Power BI together with others, the originals. If you do that, your DAX will not only be fast and SIMPLE but you'll be able to code against the model with ease 🙂 Trust me, I know what I'm saying.

 

If I get a bit of time, I'll play with this and write/generate the M code needed to achieve the above. But you should try to do it yourself as well 🙂 You'll then learn the power of M.

 

Cheers.

Anonymous
Not applicable

I appreciate your suggestions and the way you motivate @daXtreme . Almost tried to complete by learning on my own with some partial output. Thank you for taking the time to provide a referral.

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.

Top Solution Authors