Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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.
ID | Sub | Year | Month |
1 | Section1 | 2022 | Jul |
1 | Section1 | 2022 | Aug |
1 | Section1 | 2022 | Sep |
1 | Section1 | 2022 | Oct |
2 | Section2 | 2022 | Jul |
2 | Section2 | 2022 | Oct |
3 | Section3 | 2022 | Aug |
3 | Section3 | 2022 | Sep |
3 | Section3 | 2022 | Oct |
Expected output: ( Only these values has to present as table in report )
2 | Section2 | 2022 | Aug |
2 | Section2 | 2022 | Sep |
3 | Section3 | 2022 | Jul |
Kindly suggest any possibilities to resolve it in power bi, Thanks in advance!
Solved! Go to Solution.
Here you are. File attached.
@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.
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.
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.
User | Count |
---|---|
47 | |
28 | |
23 | |
18 | |
15 |
User | Count |
---|---|
55 | |
34 | |
18 | |
17 | |
15 |