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
Nanaki
Helper II
Helper II

Alarm when you forget to insert values

Hi,

I need an alarm or signal in my Power Bi dashboard.
Every month someone will put values in my dashboard. For example: In one Excel-table costs in another table Volumes and in another table recharged costs. 
Is it possible to produce an alarm or signal which shows, when you forget to insert values on one table.
For example: I insert the costs values and the volume values but i forgot the recharged costs values and then the alarm shows of in my Power bi dashboard, with the signal: You forgot the recharged values for month August.

Thanks in advance for your help.

Best regards:

Lennart 

 

1 ACCEPTED SOLUTION

Hello @Nanaki

for your case, you need a [month not] column for the filter.

Just adjust the formula as follows:

Warning Table = 
VAR MissingVolume = ADDCOLUMNS(EXCEPT(CALCULATETABLE(VALUES('Period Table'[Month Name]),FILTER('period table','period table'[Month No]<=MONTH(TODAY()))), VALUES('Volume Table'[Month Name])), 
                "Table Missing Data",  " Volume Table")
VAR MissingCosts = ADDCOLUMNS(EXCEPT(CALCULATETABLE(VALUES('Period Table'[Month Name]),FILTER('period table','period table'[Month No]<=MONTH(TODAY()))), VALUES('Costs Table'[Month Name])), 
                "Table Missing Data", " Costs Table")
RETURN
UNION(MissingVolume,  MissingCosts)

Replace VALUES('Period Table'[Month Name]) with CALCULATETABLE(VALUES('Period Table'[Month Name]),FILTER('Period Table','Period Table'[Month No]<-MONTH(TODAY())))

and here's the sample pbix file, please try it out.

Best regards

Lin

Community Support Team _ Lin
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

6 REPLIES 6
PaulDBrown
Community Champion
Community Champion

@Nanaki 

 

Here is one option. I am using a simple dataset with three tables (Costs Table, Volume Table, and Other Table)

1) Create a Data Table or Period Table for unique values of date or month (better done in Power Query but you can do it using DAX). Let's call this table "Period Table" for the purpose of this example.

 

2) Create a new table (in the Home ribbon, "New Table") to identify missing months from tables:

I've used this code:

 

 

Warning Table = 
VAR MissingVolume = ADDCOLUMNS(EXCEPT(VALUES('Period Table'[Month Name]), VALUES('Volume Table'[Month Name])), 
                "Table Missing Data",  " Volume Table")
VAR MissingOther = ADDCOLUMNS(EXCEPT(VALUES('Period Table'[Month Name]), VALUES('Other Table'[Month Name])), 
                "Table Missing Data", " Other Table")
VAR MissingCosts = ADDCOLUMNS(EXCEPT(VALUES('Period Table'[Month Name]), VALUES('Costs Table'[Month Name])), 
                "Table Missing Data", " Costs Table")
RETURN
UNION(MissingVolume, MissingOther, MissingCosts)

 

 

 

Table.JPG

 

Now create a table visual with this new table to get you this:

Result.JPG

 

You can also build this table in Power Query if you prefer. (There is an example in the attached file)

I've included a PBIX file for your reference





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Hi @PaulDBrown thank you for your reply,

this works great till now, but there is one problem.
I created the Period Table from January - December.
And in my visual table are now all missing months. Even also November and December but for this month aren't values avialable.
Is it possible to creat a month table which update automaticaly every month?
Or is it possible to tell my visual table, "just show month till today" ?


@Nanaki 

Which method did you follow to create the table? Using Power Query or Dax?

Can you post a screenshot of the table view of the created table ("Warning Table" in my example)?

Are you creating the table visual using the fields from the created table  ("Warning Table" in my example)?

 

To solve the period table problem, you should create the period table either by:

1) Preferably in Power Query by creating new tables by referencing the fact tables, isolating the month (and any other period columns- year for example), remove the rest of the columns, append these new tables and remove duplicate values

 

2) Alternatively, you can create the period table using DAX, using something along the lines of (choose new table in the ribbon):
Period Table = 
VAR CostsMonths = DISTINCT(Costs Table [Month])
VAR VolumenMonths = DISTINCT(Volumne Table [Month])
VAR OtherMonths = DISTINCT(Other Table [Month])
RETURN
DISTINCT(UNION(CostsMonth, VolumeMonth, OtherMonth)

 

Both these options will update automatically.





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Hi,

@PaulDBrown , I created the period table, just by inserting the names of the month, by creating a new table.

Nanaki_0-1602494689652.png

This is what my warning table looks like.

I tried both of your options to creat a new period table.
However my powerbi skills arent that good, I didn't get a working table.

Hello @Nanaki

for your case, you need a [month not] column for the filter.

Just adjust the formula as follows:

Warning Table = 
VAR MissingVolume = ADDCOLUMNS(EXCEPT(CALCULATETABLE(VALUES('Period Table'[Month Name]),FILTER('period table','period table'[Month No]<=MONTH(TODAY()))), VALUES('Volume Table'[Month Name])), 
                "Table Missing Data",  " Volume Table")
VAR MissingCosts = ADDCOLUMNS(EXCEPT(CALCULATETABLE(VALUES('Period Table'[Month Name]),FILTER('period table','period table'[Month No]<=MONTH(TODAY()))), VALUES('Costs Table'[Month Name])), 
                "Table Missing Data", " Costs Table")
RETURN
UNION(MissingVolume,  MissingCosts)

Replace VALUES('Period Table'[Month Name]) with CALCULATETABLE(VALUES('Period Table'[Month Name]),FILTER('Period Table','Period Table'[Month No]<-MONTH(TODAY())))

and here's the sample pbix file, please try it out.

Best regards

Lin

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

@v-lili6-msft , Thank you very much now it works!

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.