Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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
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)
Now create a table visual with this new table to get you this:
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
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" ?
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.
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.
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
User | Count |
---|---|
125 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |