Difference between direct query and Live connections

 Hi All,

The below link would give us difference between live connection and direct query

https://radacad.com/directquery-live-connection-or-import-data-tough-decision

RowNumber in dax

 Hi All,

If you want Rownumber in DAX use RANKx

=Rankx(Tablename,columnname)

Viola..you have your distinct row number :)

Not able to open Model.bim in Visual studio Analysis tabular services 2019

 Hi All,

When it rains it pours :(

When the deadline is next day and when you are close to solution and suddenly your Visual studio file is frozen and then it does not open for hours....

Finally what worked is Import the model in Visual studio instead on opening the existing one 

Puff!!

Not able to delete a visual or a tile in Power BI

 Hi All,

I have 20 Visual in a page and I could see that I was not able to delete few KPI in my page. 

When I right click on that visual I could not find the delete option. I was able to find the "Focus Mode"

Resolution - Click on "Focus Mode". 

2. Three dots would then appear.

3. Click on it and option to delete the page would come up

Enjoy Developing!

Not able to delete a page in Power BI File

 Hi All,

I duplicated one page and the for some reason I was not able to delete this page. I stayed with this issue for 2 to 3 months. Later I found the resolution for this.

Fix - Publish it to workspace

1. Pages would be hidden

2. Unhide Page

3. Make the contents of the page which you want to delete empty i,e remove all visuals from it

4. If you want to retain that page then dupliace that page and automatically the option to delete the current page comes up

5. Unhide required pages and then delete the not required Page.

Bingo! Issue is fixed :)

Power BI Report does not show remaining pages after publishing to workspace in Reading View

 Hi All,

After I publish my Power BI report to workspace, in reading view I could not see the second page or remaining pages. 
To enable this feature

Click on File > Settings 

And then click on Save. Bingo! Issue is fixed

Slicer date addition in Power BI to reflect from minimum date till yesterday's date

 Hi All,

When you have a slicer in your dashboard that gets data from a calendar table saved in SSAS tabular model and then your requirement changes that the slicer should display max date as Yesterday's date

Apply the below DAX formula to your calendar and Bingo, issue is fixed !

=CALENDAR ("1/1/2015",TODAY() - 1)

Unexpected error in Power BI Visuals after migrating to production environment

 Hi All,
I moved my Analysis tabular model cube to production and changed the settings in Power BI. No Visuals were working and all gave me same error Unexpected error. When I clicked on Copy message it told me a lot of DirectQuery features disabled.

I spent time in enabling these features but Alas ..this was not the fix. 

The fix turned out to install the latest cumulative update for the SQL server version installed in production. 

https://support.microsoft.com/en-us/help/2876882/kb2876882-fix-internal-error-an-unexpected-error-occurred-when-you-run

Enjoy deploying!!

Spacing between bar chart columns in Power Bi

 Hi All,

I had a requirement where I have to introduce spaces between bar chart columns. Since this is not a default option present in power BI, I had to apply for workaround. 
The steps in below link helped me achieve it

https://community.powerbi.com/t5/Desktop/Spacing-in-column-chart/m-p/423641#M194839

Enjoy!!

To find the version details of the SSAS Analysis server

 Hi All,
Follow the below steps to find the version details of SSAS Analysis server


 SSMS: connect to your SSAS.

* Right-click Server

* Reports

* Standard Reports

* General

Date functions in DAX

Hi All,

Learnt few date functions in DAX

1 - To obtain Week Num  = =WeekNum(DateColumn, 2)

2 - if you want day to start from Monday and 1 - if you want Sunday

2 - To obtain Month name

=Format(DateColumn,"MMM")

3 - To obtain Day name

=Format(DateColumn,"dddd")

Power BI DAX - RELATIONSHIP IS many to many relationship which is currently not supported

 Hi All,

Error while creating relationship between two tables in SSAS tabular model - RELATIONSHIP IS many to many relationship which is currently not supported.

To resolve this, create a bridge table and then establish One to many and Many to One relationship between the tables.

You may use the below DAX query to create Distinct value

=DISTINCT(D_EXCHANGERATE[CD_FROMCUR])

Enjoy coding!

Hi All,

Error message  - An invalid numeric representation of a date value was encountered

Root cause - We are trying to compare with a measure value

Workaround - The workaround suggested by the documentation is to filter the Date table and pass it as a table filter to calculate. This requires ignoring first the filter context on the Date table, only to overwrite it later with the ‘as of date’.

Solution - =CALCULATE( SUM(Sales[SalesAmount]), FILTER( ALL( ‘Date’[CalendarYear]), [CalendarYear] = MAX(‘Date’[CalendarYear]) ) )

Refer below link for solution

https://prologika.com/dax-variables-to-rescue/

To create Date Dimension in SSAS Tabular model

 Hi All,

To create date dimension in SSAS tabular model

Click on calculated table and then paste the below in the formula

=CALENDAR ("1/1/2015","1/1/2030")

And then give the date table your name and follow the below steps to make it effective

In Visual Studio, in the model designer, click the date table.

Click Extensions > Table > Date > Mark as Date Table.

In the Mark as Date Table dialog box, in the Date listbox, select a column to be used as a unique identifier. This column must contain unique values and should be of Date data type.

If necessary, create any relationships between fact tables and the date table.

Create many to many relationship in Tabular model

Hi All,

The below saved my time today. Many to Many is supported with SQL server compatibility level 1500 or more.My development server did not have this compatibility

This is one of tip for BI Architect. Take all latest versions of tools so that we will never get any compatibility issues and we will be able to use all best features available. 

Anyways below link helped me achieve my results. 

 https://www.google.com/search?safe=active&rlz=1C1GCEB_enIN896IN897&sxsrf=ALeKk00cv_-3yUUH3JuF4vvNzJVcgGsOqA%3A1600131070409&ei=_g9gX-XKGKvrz7sP_oOy2AM&q=create+many+to+many+relationship+power+bi&oq=create+many+to+many+&gs_lcp=CgZwc3ktYWIQAxgFMgIIADICCAAyAggAMgIIADICCAAyBwgAEBQQhwIyAggAMgIIADICCAAyAggAOgQIABBHOgUIABCRAjoECAAQQzoICC4QxwEQowI6BAgjECc6CwguELEDEMcBEKMCOgoIABCxAxCDARBDOgUIABCxA1DnsJYEWLPQlgRgmOiWBGgAcAJ4AIAB6gGIAegXkgEGMC4xNi40mAEAoAEBqgEHZ3dzLXdpesgBCMABAQ&sclient=psy-ab#kpvalbx=_OjJgX9_yCvvhz7sP-cSjiAU39

Thank you so much God for being with me and helping me

To obtain selected value from Date Slicer in Power BI

 Hi All,

Well I did not spend much time to find answer to this valuable question

Resolution - CAL_DT_VALID_TO =

CALCULATE (MAX (D_SALES_STATE_HIST[DT_DOCUMENT]), ALLSELECTED (D_SALES_STATE_HIST[DT_DOCUMENT] ) )

Enjoy coding !!

SWITCH STATEMENT IN DAX

 Hi Friends!

Spent the whole day in understanding how to implement the switch statement in Direct query , tabular model. 

I finally found workaround for it. Include the switch statement in filter columns. I have used EXCEPT to exclude the rows that I don't need

CALCULATE(DISTINCTCOUNT(D_SF_VIP_ACCOUNT[CD_VIP_ACCOUNT]),

EXCEPT

(

FILTER(

SUMMARIZE(D_SALES_STATE_HIST,

D_SALES_STATE_HIST[CD_SYSCOUNTRY],

D_SF_VIP_ACCOUNT[CD_COUNTRY_OF_RESIDENCE],

D_SF_VIP_ACCOUNT[CD_NATIONALITY],

"VT", DISTINCTCOUNT(D_SF_VIP_ACCOUNT[CD_VIP_ACCOUNT])),

(D_SF_VIP_ACCOUNT[CD_COUNTRY_OF_RESIDENCE] = "CHN") ),

FILTER(

SUMMARIZE(D_SALES_STATE_HIST,

D_SALES_STATE_HIST[CD_SYSCOUNTRY],

D_SF_VIP_ACCOUNT[CD_COUNTRY_OF_RESIDENCE],

D_SF_VIP_ACCOUNT[CD_NATIONALITY],

"VT", DISTINCTCOUNT(D_SF_VIP_ACCOUNT[CD_VIP_ACCOUNT])),

(MID(D_SALES_STATE_HIST[CD_SYSCOUNTRY],2,5) = "AUS" && D_SF_VIP_ACCOUNT[CD_COUNTRY_OF_RESIDENCE] = "NZL") ||

(MID(D_SALES_STATE_HIST[CD_SYSCOUNTRY],2,5) = "AUS" && D_SF_VIP_ACCOUNT[CD_COUNTRY_OF_RESIDENCE] = "AUS") ||

(MID(D_SALES_STATE_HIST[CD_SYSCOUNTRY],2,5) = "THD" && D_SF_VIP_ACCOUNT[CD_COUNTRY_OF_RESIDENCE] = "THA") ||

(MID(D_SALES_STATE_HIST[CD_SYSCOUNTRY],2,5) = "KLM" && D_SF_VIP_ACCOUNT[CD_COUNTRY_OF_RESIDENCE] = "KOR") ||

(MID(D_SALES_STATE_HIST[CD_SYSCOUNTRY],2,5) = "HKG" && D_SF_VIP_ACCOUNT[CD_COUNTRY_OF_RESIDENCE] = "MAC") ||

(MID(D_SALES_STATE_HIST[CD_SYSCOUNTRY],2,5) = "HKG" && D_SF_VIP_ACCOUNT[CD_COUNTRY_OF_RESIDENCE] = "HKG") ||

(MID(D_SALES_STATE_HIST[CD_SYSCOUNTRY],2,5) = "HKG" && D_SF_VIP_ACCOUNT[CD_NATIONALITY] = "MAC") ||

(MID(D_SALES_STATE_HIST[CD_SYSCOUNTRY],2,5) = "HKG" && D_SF_VIP_ACCOUNT[CD_NATIONALITY] = "HKG") ||

(MID(D_SALES_STATE_HIST[CD_SYSCOUNTRY],2,5) = D_SF_VIP_ACCOUNT[CD_COUNTRY_OF_RESIDENCE]) ||

ISBLANK(D_SF_VIP_ACCOUNT[CD_COUNTRY_OF_RESIDENCE])

)

)

Be Happy !

The column '...' specified in the 'SUMMARIZE' function was not found in the input table.

 Hi All,

To resolve above error 

1. check if you are connected and using the right Analysis tabular model.

2. Also check the syntax of the query, you might be missing a comma

3. If you are using the Summarize function then include the missing table as the first table name

Enjoy!

The expression refers to multiple columns, Multiple columns cannot be converted to a scalar value!

 Hi All,

How will you feel when you have spent 24 hours on finding a solution for an issue and suddenly a fluke code work for you ? Happy..? Thank God ? What else..? 

Well for me its excitement!

Fix for the above issue is as below

Enjoy Life!

The function COUNT takes an argument that evaluates to numbers or dates and cannot work with values of type String.

 Hi All,

So we try to read on what COUNT, COUNTX, COUNTA and all the other types of COUNT and we go nuts trying to figure out its difference

Finally I was able to understand the difference between COUNT and COUNTA

COUNT - would count int and date type columns only

COUNTA - would count text values. 

So if you encounter the error listed in the header then use COUNTA instead of COUNT

Enjoy coding !!

DAX - Get the date difference in days from the slicer in Power BI

Hi All, 

 To get the date difference in days from the slicer in Power BI, follow below code

DAYSSELECTED =

VAR FirstDay = CALCULATE(

MIN('D_SALES_STATE_HIST'[DT_DOCUMENT]),

ALLSELECTED('D_SALES_STATE_HIST'[DT_DOCUMENT])

)

VAR LastDay = CALCULATE(

MAX('D_SALES_STATE_HIST'[DT_DOCUMENT]),

ALLSELECTED('D_SALES_STATE_HIST'[DT_DOCUMENT])

)

RETURN

DATEDIFF (FirstDay,LastDay,DAY)

Create relationships for more than 1 column in SSAS tabular mode

 Hi All,

To Create relationships for more than 1 column in SSAS tabular mode was difficult as there is no provision for this in the Data model view. To achieve this, I had to create a calculated column and then create relationships. 

Follow the below link to understand how to calculate the calculated column 

https://www.sqlshack.com/creating-first-ssas-tabular-model-database/

Convert decimal to whole number in measure in Power BI

 Hi All,

Some small things but it would be great things for a beginner. Here is how to format the measure I,e convert decimal to whole number in Power BI

Click on measure and at the top menu, you would find the format bar in which you can change it to Whole number 

Data overflow converting to the data type for table 'D_ACC' column 'DT_BIRTH'. The current operation was cancelled because another operation in the transaction failed.

 Hi All,

Okay finally found resolution for this issue which was bugging me from past 2 days

Issue - Data overflow converting to the data type for table 'D_ACC' column 'DT_BIRTH'. The current operation was cancelled because another operation in the transaction failed.

Resolution - Identify the data type of column that is causing the issue and apply convert function. What worked for me is the below code

SELECT CONVERT(VARCHAR(10), DT_BIRTH, 120) FROM D_ACC

Note - While trying to resolve this issue, I encountered another  error

Error 2 - Capacity exceeds maximum capacity. Parameter name: capacity

Resolution - I understood that I was trying to add all column names and the editor did not like so many character at once. So I clicked on "Design View", manually pulled the table, which then showed me all column names and then I entered the code CONVERT(VARCHAR(10), DT_BIRTH, 120) as DT_BIRTH.

Bingo!!!!

Am able to load huge chunk of data in my SSAS tabular model

Unable to save the file model.bim readonly

 Hi All,

Issue - "Unable to save the file model.bim readonly" error while trying to save the tabular cube.

Resolution -

– Click on the Model.bim file, and select ‘Properties’ and point the tabular model at the correct instance

– Click on the Project, and select the updated Properties information under ‘server’.

Enjoy 😃

Database already exists in dettached state - Analysis Tabular services

 Hi All,

Issue - database already exists in dettached state

Resolution - To add folder in "Allow Browsing folders". Check the below link from 8th minute till the end

https://www.youtube.com/watch?v=cvn4z7AKMFg

😃

Tabular model explorer missing

 Hi Folks!

Okay..so here starts my first tabular analysis services project. 

First error

Problem - Tabular model explorer missing

Resolution - Double click on Model and you would find your tabular explorer

CMMi Artifacts

 Hi Folks,

Find attached PPT which would be getting uploaded daily and has information on CMMi Artifacts

https://drive.google.com/file/d/17yMDH1a4lASPgorLGJEj1kHTdvoMlj_9/view

Updated Artifacts with definitions only :)

https://drive.google.com/file/d/1hVPApy8Bt-2UJDn-t4oJD-337xidtv0W/view?usp=sharing

GetData is disabled in PowerBI Desktop

 Hi All,

Today I learnt a new feature in Power BI which is "Live Connections"

Got a request from a customer that they want to import excel sheet to the power BI file and make some changes to it. When I took control of the screen, could see that I GETDATA tab is disabled. Upon further research on this issue, I could find lot of information on Live connection which is listed in below lin 

https://radacad.com/live-connection-when-power-bi-comes-hybrid

Now the only possible solution is to request the team who is responsible for creating the data model to include the excel sheet data and then add new measures in Power BI file. 

Happy Working 👏😃

SSRS graph axis label interval is not continuous. And SSRS date in graph x axis is not sorted

Hi Folks!
Do I love my job or Do I love my job...
I am so happy to be able to fix issues so quickly.

One of my colleague came to me with two issues in SSRS.

Issue 1 - SSRS graph axis label interval is not continuous
I,e they get the below type of graph



What is expected is below



To achieve this, you have to set 1 in Labels under properties.



And then the next issue was the dates were not  sorted.
Well...just type in below code and Bingo..issue is fixed

=Format(Fields!MyDateField,"dd/mm/yyyy")

Happy helping !!