03-13-2017 05:11 AM - edited 03-13-2017 05:17 AM
New to Power BI so please bear with me.
I have 3 tables.
Date: All of 2017 dates + countries.
Rev: Has the number of orders and USD Revenue by date/country
Traffic: HAs number of website sessions by date/country
The dashboard slider is the dates from Date sheet. I have created relationship between the tables by creating a column, Date&Country.
While trying to setup a card for USD Revenue, I keep getting (Blank). I have checekd the data type under modelling and it shows decimals.
Also, for transactions, I have created a measure to count the number of transactions, based on the slicer setting.
Transactions = counta(Revenue[Order #]) . Again, this returns a (Blank) when I use a card.
Any idea why I keep getting (Blank)?
07-02-2017 05:51 AM - edited 07-02-2017 05:56 AM
A better solution would be to "re-model" the data.
The Date table has values for both dates and countries - split them into separate tables either using Power Query / M or use the DAX language in the data model itself.
Using DAX select the "Modeling" tab and press "New Table" button. Write the following formula:
Dates = VALUES( 'Date'[Date] )
Press "New Table" button once more and write:
Countries = VALUES( 'Date'[Country] )
In the resulting data model hide the original 'Date' table by right-clicking its name in the right-most pane and choose "Hide". Then create relationships between each of the new tables to your fact-tables 'Rev' and Traffic'.
Delete ANY relationship to the original 'Date' table.
You should end up with a data model similar to this:
You can then filter the data in the report on 'Dates'[Date] and/or 'Countries'[Country].
If the card visual used in the report still shows a (Blank) its because the combination of filters on the 'Dates' and 'Countries' does not exist.
The model above assumes that ALL valid combination of dates and countries are found in the original 'Date' table. Otherwise you might end up with "orphaned records" in either 'Rev' or 'Traffic' table - thats a record where the date or country in the table is not present in the calculated tables 'Dates' or 'Countries'.
Solving orphaned records is a slightly more complex solution - so I won't go into details here.